package org.compiere.model;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.DBException;
import org.compiere.model.X_hrm_employee;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Employee;

import extend.org.compiere.utils.Util;


public class Mhrmemployee extends X_hrm_employee {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1688713614369792890L;

	public Mhrmemployee(Properties ctx){
		super(ctx, null, null);
	}
	
	public Mhrmemployee(Properties ctx, int hrm_employee_ID, String trxName) {
		super(ctx, hrm_employee_ID, trxName);
	}
	
	/**
	 *  Load Constructor
	 *  @param ctx context
	 *  @param rs result set record
	 *  @param trxName transaction
	 */
	public Mhrmemployee (Properties ctx, ResultSet rs, String trxName)
	{
		super(ctx, rs, trxName);
	}	//	Mhrmemployee
	
	// Error message
	private StringBuilder errmsg = new StringBuilder();
		
	@Override
	protected boolean beforeSave(boolean newRecord) {
				
		if(gethrm_percentBonus().intValue()!=0 &&
				gethrm_amountbonus().intValue()!=0){
			errmsg.append("Заполните только одно из полей. \n"+  Msg.translate(Env.getCtx(), "Field")+": "+Msg.translate(Env.getCtx(), COLUMNNAME_hrm_amountbonus)+", ");
			errmsg.append(Msg.translate(Env.getCtx(), COLUMNNAME_hrm_percentBonus)+" \n");
			Util.checkErrMsg(errmsg);
		}
		
		//
		X_HR_Employee hremp = X_HR_Employee.getHR_Employee(getC_BPartner_ID(),true);
		X_hrm_order hrmorder = new X_hrm_order(getCtx(), gethrm_order_ID(), null);

		if(hrmorder.gethrm_ordertype().equals(X_hrm_order.HRM_ORDERTYPE_Moving)
				|| hrmorder.gethrm_ordertype().equals(X_hrm_order.HRM_ORDERTYPE_Assignment)){
			hrmorder.sethrm_departmentTo_ID(hremp.getHR_Department_ID());
			hrmorder.sethrm_tojob_ID(hremp.getHR_Job_ID());
			hrmorder.saveUpdate();
		}
		
		return true;
	}
	
	/**
	 * Get Order (Employee) list 
	 * @param orderType
	 * @return
	 */
	public Mhrmemployee[] getHRMEmployeeList (int block_id, 
			int department_id, 
			String orderType, 
			String subtype, 
			Timestamp startDate, 
			Timestamp  endDate,
			String isManagement)
	{
		
		Mhrmemployee hrmempl = new Mhrmemployee(getCtx(),0,get_TrxName());
		
		String sql_ = "SELECT HRM_EMPLOYEE.* FROM HRM_EMPLOYEE \n" +
				"LEFT JOIN HR_EMPLOYEE ON HR_EMPLOYEE.C_BPARTNER_ID=HRM_EMPLOYEE.C_BPARTNER_ID \n" +
				"AND HR_EMPLOYEE.ENDDATE = (SELECT MAX(H.ENDDATE) FROM HR_EMPLOYEE H WHERE H.C_BPARTNER_ID=HR_EMPLOYEE.C_BPARTNER_ID) \n" +
				"JOIN HRM_ORDER ON HRM_ORDER.HRM_ORDER_ID=HRM_EMPLOYEE.HRM_ORDER_ID \n" +
				"LEFT JOIN HR_JOB ON HR_JOB.HR_JOB_ID=HR_EMPLOYEE.HR_JOB_ID \n" +
				"WHERE HRM_EMPLOYEE.AD_CLIENT_ID=? \n";
		
		StringBuffer sql = new StringBuffer(sql_);
	
		List<Object> param = new ArrayList<Object>();
		param.add(new BigDecimal(getAD_Client_ID()));		
		
		if (!orderType.isEmpty()){
			sql.append(" AND (HRM_ORDER.HRM_ORDERTYPE=?");
			param.add(orderType);
			if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_BusinessTrip)){
				sql.append(" OR HRM_ORDER.HRM_ORDERTYPE=?) \n");
				param.add(X_hrm_order.HRM_ORDERTYPE_BossTrip);
			}
			else if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_Vacation)){
				sql.append(" OR HRM_ORDER.HRM_ORDERTYPE=?) \n");
				param.add(X_hrm_order.HRM_ORDERTYPE_BossVacation);

			}
			else
				sql.append(") \n");
		}		
		else
			sql.append(" AND 1=2 \n");
		
		if(startDate != null && endDate != null){
			sql.append(" AND HRM_ORDER.STARTDATE BETWEEN ? AND ? \n");
			param.add(startDate);param.add(endDate);
		}		
		else
			sql.append(" AND 1=2 \n");
		
		if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_Vacation) && subtype != null){
			sql.append(" AND HRM_ORDER.HRM_WORKLEAVETYPE=? \n");
			param.add(subtype);
		}
		else if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_BusinessTrip)  && subtype != null){
			sql.append(" AND HRM_ORDER.HRM_BUSINESSTRIPTYPE=? \n");
			param.add(subtype);
		}
		else if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_Dismissal)  && subtype != null){
			sql.append(" AND HRM_ORDER.HRM_CONTRACTTERMINATIONTYPE=? \n");
			param.add(subtype);
		}
		else if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_Premium)  && subtype != null){
			sql.append(" AND HRM_ORDER.HRM_BONUSTYPE=? \n");
			param.add(subtype);
		}
		
		if (!orderType.isEmpty() && department_id != 0){
			sql.append(" AND HR_EMPLOYEE.HR_DEPARTMENT_ID=? \n");
			param.add(department_id);
		}
		else if(!orderType.isEmpty() && block_id !=0){
			sql.append(" AND HR_EMPLOYEE.HR_DEPARTMENT_ID IN (SELECT NODE_ID FROM AD_TREENODEDEP WHERE PARENT_ID=?) \n");
			param.add(block_id);
			
		}
		
		if(isManagement.equals("Y"))
			sql.append(" AND HR_JOB.ISMANAGEMENT='Y' \n");
		else if(isManagement.equals("N"))
			sql.append(" AND HR_JOB.ISMANAGEMENT='N' \n");
		
		// Order
		if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_Vacation))
			sql.append("ORDER BY HRM_ORDER.HRM_WORKLEAVETYPE, HR_EMPLOYEE.HR_DEPARTMENT_ID");
		else if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_BusinessTrip))
			sql.append("ORDER BY HRM_ORDER.HRM_BUSINESSTRIPTYPE, HR_EMPLOYEE.HR_DEPARTMENT_ID");
		else if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_Dismissal))
			sql.append("ORDER BY HRM_ORDER.HRM_CONTRACTTERMINATIONTYPE, HR_EMPLOYEE.HR_DEPARTMENT_ID");
		else 
			sql.append("ORDER BY HR_EMPLOYEE.HR_DEPARTMENT_ID");
		
		List<Mhrmemployee> list = new ArrayList<Mhrmemployee>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			pstmt = DB.prepareStatement (sql.toString(), get_TrxName());
			DB.setParameters(pstmt, param);
			rs = pstmt.executeQuery();
			while (rs.next ())
			{
				hrmempl = new Mhrmemployee(getCtx(), rs, get_TrxName());
				list.add(hrmempl);
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
			throw new DBException(e, sql.toString());
		} finally {
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return list.toArray(new Mhrmemployee[list.size()]);
	}

}
